﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Agile.Engine.Utils;

namespace Agile.Engine.Data
{
    public class DataModel
    {

        #region Properties

        public DataContext DataContext { get; set; }

        #endregion

        /// <summary>
        /// 查询并创建数据表
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public System.Data.DataTable QueryTable(System.Data.CommandType cmdType, string tableName, string cmdText)
        {
            var data = DataContext.DataTemplate.DataTableCreate(cmdType, cmdText);
            data.TableName = tableName;
            return data;
        }

        /// <summary>
        /// 查询单表全部信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable QueryTable(string tableName)
        {
            var s = "SELECT * FROM {0}";
            return QueryTable(CommandType.Text, tableName, string.Format(s, tableName));
        }

        /// <summary>
        /// 查询过滤信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public DataTable QueryTable(string tableName, string filter)
        {
            var s = "SELECT * FROM {0} WHERE 1=1 AND {1}";
            return QueryTable(CommandType.Text, tableName, string.Format(s, tableName, filter));
        }

        /// <summary>
        /// 按指定pkey字段更新
        /// </summary>
        /// <param name="table"></param>
        /// <param name="pkey"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public DataRow UpdateTable(string table, string pkey, DataRow data)
        {
            var filter = string.Format("{0} in ('{1}')", pkey, data[pkey]);

            var dt = QueryTable(table, filter);
            DataRow newRow = dt.NewRow();

            var exist = DataUtils.ScalarRow(dt, pkey, data[pkey]);
            if (exist != null)
            {
                exist[0].ItemArray = data.ItemArray;
                newRow = exist[0];
            }
            else
            {
                dt.Rows.Add(data.ItemArray);
            }
            UpdateTable(dt);
            return data;

        }

        /// <summary>
        /// 更新指定数据表，按data.TableName
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public bool UpdateTable(DataTable data)
        {
            var s = "SELECT * FROM {0}";
            UpdateTable(data, string.Format(s, data.TableName));
            return true;
        }

        /// <summary>
        /// 更新数据表，按查询语句
        /// </summary>
        /// <param name="data"></param>
        /// <param name="select"></param>
        /// <returns></returns>
        public bool UpdateTable(DataTable data, string select)
        {
            DataContext.DataTemplate.DataTableUpdateWithCommandBuilder(data, CommandType.Text, select, null, data.TableName);
            return true;
        }

        /// <summary>
        /// 按过滤设置更新表
        /// </summary>
        /// <param name="data"></param>
        /// <param name="tableName"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public bool UpdateTable(DataTable data, string tableName, string filter)
        {
            data.TableName = tableName;
            var sql = "SELECT * FROM {0} WHERE 1=1 AND {1}";
            return UpdateTable(data, string.Format(sql, tableName, filter));
        }


        /// <summary>
        /// 取指定table指定field的max值
        /// </summary>
        /// <param name="table"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public decimal Max(string table, string field)
        {
            var s = "SELECT MAX({0}) FROM {1}";
            return ExecuteScalar(0m, string.Format(s, field, table));
        }

        /// <summary>
        /// 取指定table指定field的max值
        /// </summary>
        /// <param name="table"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public decimal Max(string table, string field, string filter)
        {
            var s = "SELECT MAX({0}) FROM {1} WHERE {2}";
            return ExecuteScalar(0m, string.Format(s, field, table, filter));
        }


        /// <summary>
        /// 取指定table指定field的count值
        /// </summary>
        /// <param name="table"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public decimal Count(string table, string field)
        {
            var s = "SELECT COUNT({0}) FROM {1}";
            return ExecuteScalar(0m, string.Format(s, field, table));
        }

        /// <summary>
        /// 取指定table指定field的count值
        /// </summary>
        /// <param name="table"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public decimal Count(string table, string field, string filter)
        {
            var s = "SELECT COUNT({0}) FROM {1} WHERE {2}";
            return ExecuteScalar(0m, string.Format(s, field, table, filter));
        }


        /// <summary>
        /// 查询首行首列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="defaultValue"></param>
        /// <param name="select"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(T defaultValue, string select)
            where T : struct
        {
            var scalar = DataContext.DataTemplate.ExecuteScalar(CommandType.Text, select);
            return DataUtils.Parse(defaultValue, scalar);
        }

        /// <summary>
        /// 按filter条件删除
        /// </summary>
        /// <param name="table"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public int Delete(string table, string filter)
        {
            var s = "DELETE FROM {0} WHERE 1=1 AND {1}";
            return ExecuteNonQuery(CommandType.Text, string.Format(s, table, filter));
        }

        /// <summary>
        /// 删除数据行
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="del"></param>
        /// <returns></returns>
        public bool Delete(string tableName, DataRow[] del)
        {
            DataTable dt = del.CopyToDataTable();
            dt.TableName = tableName;
            dt.AcceptChanges();

            foreach (var r in dt.AsEnumerable())
                r.Delete();

            return UpdateTable(dt);
        }

        /// <summary>
        /// 执行非查询语句
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 执行非查询语句
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string sql)
        {
            return DataContext.DataTemplate.ExecuteNonQuery(CommandType.Text, sql);
        }


        /// <summary>
        /// 执行事务
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="func"></param>
        /// <param name="arg"></param>
        /// <returns></returns>
        public bool ExecuteTransaction(Action action)
        {
            Spring.Data.Core.AdoPlatformTransactionManager adoTrans
                = new Spring.Data.Core.AdoPlatformTransactionManager(DataContext.DataProvider);

            Spring.Transaction.Support.DefaultTransactionDefinition def
                = new Spring.Transaction.Support.DefaultTransactionDefinition();
            def.PropagationBehavior = Spring.Transaction.TransactionPropagation.Required;


            var transactionStatus = adoTrans.GetTransaction(def);
            try
            {
                action.Invoke();
            }
            catch
            {
                adoTrans.Rollback(transactionStatus);
                throw;
            }

            adoTrans.Commit(transactionStatus);
            return true;
        }

    }
}
